package com.ph.irp.base.assistantmaterial.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialDeliverymethodDao;
import com.ph.irp.model.PBaseAssistantmaterialDeliverymethod;
import com.ph.util.DBUtil;

public class PBaseAssistantmaterialDeliverymethodDaoImpl implements
		PBaseAssistantmaterialDeliverymethodDao {
	private ComboPooledDataSource dataSource;
	
	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addDeliverymethod(
			PBaseAssistantmaterialDeliverymethod deliveryMethod)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("insert into p_base_assistantmaterial_deliverymethod (DeliveryMethodCode,DeliveryMethodName)values(");
		sb.append("'").append(deliveryMethod.getDeliveryMethodCode().trim()).append("',");
		sb.append("'").append(deliveryMethod.getDeliveryMethodName().trim()).append("')");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		}  
		finally{
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void deleteDeliverymethod(int deliveryMethodId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		String sql = "delete from p_base_assistantmaterial_deliverymethod where DeliveryMethodID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, deliveryMethodId);
			pStmt.execute();
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		}  
		finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public PBaseAssistantmaterialDeliverymethod getDeliverymethodId(
			int deliveryMethodId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseAssistantmaterialDeliverymethod deliveryMethod = null;
		String sql = "select * from p_base_assistantmaterial_deliverymethod where DeliveryMethodID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, deliveryMethodId);
			rs = pStmt.executeQuery();
			if(rs.next()){
				deliveryMethod = new PBaseAssistantmaterialDeliverymethod();
				deliveryMethod.setDeliveryMethodId(rs.getInt("DeliveryMethodID"));
				deliveryMethod.setDeliveryMethodCode(rs.getString("DeliveryMethodCode"));
				deliveryMethod.setDeliveryMethodName(rs.getString("DeliveryMethodName"));
			}
		}
		catch(Exception e){
			   throw new Exception(e.getMessage());
			} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return deliveryMethod;
	}

	@Override
	public List<PBaseAssistantmaterialDeliverymethod> getDeliverymethodList()
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDeliverymethod> list = new ArrayList<PBaseAssistantmaterialDeliverymethod>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_deliverymethod");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseAssistantmaterialDeliverymethod pbadm = new PBaseAssistantmaterialDeliverymethod();
				pbadm.setDeliveryMethodId(rs.getInt("DeliveryMethodID"));
				pbadm.setDeliveryMethodCode(rs.getString("DeliveryMethodCode"));
				pbadm.setDeliveryMethodName(rs.getString("DeliveryMethodName"));
				list.add(pbadm);
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@Override
	public boolean isSame(String deliveryMethodCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		String sql = "select * from p_base_assistantmaterial_deliverymethod where DeliveryMethodCode='"+deliveryMethodCode.trim()+"'";
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if(rs.next()){
				return true;
			}else{
				return false;
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		}  
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void updateDeliverymethod(
			PBaseAssistantmaterialDeliverymethod deliveryMethod)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_assistantmaterial_deliverymethod set DeliveryMethodCode='");
		sb.append(deliveryMethod.getDeliveryMethodCode().trim()).append("',DeliveryMethodName='");
		sb.append(deliveryMethod.getDeliveryMethodName().trim()).append("'");
		sb.append(" where DeliveryMethodID=").append(deliveryMethod.getDeliveryMethodId());
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDeliverymethodByCode(String code) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDeliverymethod> list = new ArrayList<PBaseAssistantmaterialDeliverymethod>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_deliverymethod where DeliveryMethodCode like '%"+code+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseAssistantmaterialDeliverymethod pbadm = new PBaseAssistantmaterialDeliverymethod();
				pbadm.setDeliveryMethodId(rs.getInt("DeliveryMethodID"));
				pbadm.setDeliveryMethodCode(rs.getString("DeliveryMethodCode"));
				pbadm.setDeliveryMethodName(rs.getString("DeliveryMethodName"));
				list.add(pbadm);
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDeliverymethodByCodeAndName(String code, String name)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDeliverymethod> list = new ArrayList<PBaseAssistantmaterialDeliverymethod>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_deliverymethod where DeliveryMethodCode like '%"+code+"%' and DeliveryMethodName like '%"+name+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseAssistantmaterialDeliverymethod pbadm = new PBaseAssistantmaterialDeliverymethod();
				pbadm.setDeliveryMethodId(rs.getInt("DeliveryMethodID"));
				pbadm.setDeliveryMethodCode(rs.getString("DeliveryMethodCode"));
				pbadm.setDeliveryMethodName(rs.getString("DeliveryMethodName"));
				list.add(pbadm);
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDeliverymethodByName(String name) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDeliverymethod> list = new ArrayList<PBaseAssistantmaterialDeliverymethod>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_deliverymethod where DeliveryMethodName like '%"+name+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseAssistantmaterialDeliverymethod pbadm = new PBaseAssistantmaterialDeliverymethod();
				pbadm.setDeliveryMethodId(rs.getInt("DeliveryMethodID"));
				pbadm.setDeliveryMethodCode(rs.getString("DeliveryMethodCode"));
				pbadm.setDeliveryMethodName(rs.getString("DeliveryMethodName"));
				list.add(pbadm);
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

}
